package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.DireccionDao;
import com.sophossystems.client.asmon.dto.DireccionDTO;
import com.sophossystems.client.asmon.dto.DireccionPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.Direccion;

public class DireccionDaoImpl extends AbstractDAO implements DireccionDao {

	
	private static Logger log = Logger.getLogger(ClientePMoralesDaoImpl.class);
	
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	@Override
	@Transactional
	public DireccionPkDTO insert(final DireccionDTO dto) {
		DireccionPkDTO exito = null;
		try {
			exito = (DireccionPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Transaction tx = session.beginTransaction();
							DireccionPkDTO pk = null;
							Direccion direccion = new Direccion();
							direccion.mapper(dto);
							session.save(direccion);
							tx.commit();

							try {
								pk =  findLastInsert();

							} catch (DaoException es) {
								throw new HibernateException("Insercion de direccion: "
										+ es.getMessage());
							}

							return pk;

						}

					});

		} catch (final Exception e) {
			logger.error("Error: direccion, cause: " + e.getMessage(), e);

		}
		return exito;
	}

	/**
	 * Updates a single row in the Activity table.
	 */

	@Override
	@Transactional
	public void update(final DireccionDTO dto) throws DaoException {
		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					DireccionPkDTO pk = null;
					DireccionDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdDireccion());
						pk = new DireccionPkDTO();
						pk.setIdDireccion(res.getIdDireccion());
					} catch (DaoException es) {
						throw new HibernateException("Update DireccionDao: "
								+ es.getMessage());
					}
					// /aqui se actualizar los campos y con los de la entrada
					// cuidado de actualiza todo.
					Direccion persis = new Direccion();
					persis.mapper(dto);
					persis.setIdDireccion(res.getIdDireccion());
					session.update(persis);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: "
					+ e.getMessage());
		}

	}

	/**
	 * Deletes a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void delete(final DireccionPkDTO pk) throws DaoException {

		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Transaction tx = session.beginTransaction();
					Direccion direccion = (Direccion) session.load(
							Direccion.class, pk.getIdDireccion());
					session.delete(direccion);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: "
					+ e.getMessage());
		}
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName() {
		return "Direccion";
	}

	/**
	 * Returns all rows from the DIRECCION table that match the criteria
	 * 'idDireccion = :idDireccion'.
	 */
	@Override
	@Transactional
	public DireccionDTO findByPrimaryKey(final int idDireccion)
			throws DaoException {
		DireccionDTO direccion = null;
		try {
			direccion = (DireccionDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							final Query oQuery = session
									.createQuery("SELECT d.idDireccion as idDireccion,"
											+ " d.calle as calle, d.numExt as numExt, d.numInt as numInt,"
											+ " d.colonia as colonia, d.delegacion as delegacion, d.cp as cp,"
											+ " d.entidadFed as entidadFed, d.telefono as telefono,"
											+ " d.idTipoDireccion as idTipoDireccion FROM "
											+ getTableName()
											+ " d WHERE d.idDireccion = :idDireccion");
							oQuery.setInteger("idDireccion", idDireccion);
							DireccionDTO direccionDto = (DireccionDTO) oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(DireccionDTO.class))
									.list().get(0);
							return direccionDto;
						}
					});

		} catch (Exception e) {
			logger.error("Error: DIRECCION, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return direccion;
	}

	/**
	 * Returns all rows from the DIRECCION table that match the criteria ''.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<DireccionDTO> findAll() throws DaoException {
		List<DireccionDTO> direccion = null;
		try {
			direccion = (List<DireccionDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT d.idDireccion as idDireccion,"
											+ " d.calle as calle, d.numExt as numExt, d.numInt as numInt,"
											+ " d.colonia as colonia, d.delegacion as delegacion, d.cp as cp,"
											+ " d.entidadFed as entidadFed, d.telefono as telefono,"
											+ " d.idTipoDireccion as idTipoDireccion FROM "
											+ getTableName()
											+ " d ORDER BY d.idDireccion");
							List<DireccionDTO> direccionDto = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(DireccionDTO.class))
									.list();
							return direccionDto;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
			// + getTableName() + " ORDER BY idDireccion", this);
		} catch (Exception e) {
			logger.error("Error: DIRECCION, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return direccion;
	}

	/**
	 * Returns all rows from the DIRECCION table that match the criteria
	 * 'idDireccion = :idDireccion'.
	 */
	@SuppressWarnings("unchecked")
	@Transactional
	public List<DireccionDTO> findWhereIdDireccionEquals(final int idDireccion)
			throws DaoException {
		List<DireccionDTO> direccion = null;
		try {
			direccion = (List<DireccionDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT d.idDireccion as idDireccion,"
											+ " d.calle as calle, d.numExt as numExt, d.numInt as numInt,"
											+ " d.colonia as colonia, d.delegacion as delegacion, d.cp as cp,"
											+ " d.entidadFed as entidadFed, d.telefono as telefono,"
											+ " d.idTipoDireccion as idTipoDireccion FROM "
											+ getTableName()
											+ " d WHERE idDireccion = :id_direccion ORDER BY d.idDireccion");
							oQuery.setInteger(0, idDireccion);
							List<DireccionDTO> direccionDto = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(DireccionDTO.class))
									.list();
							return direccionDto;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
			// + getTableName() + " WHERE idDireccion = ? ORDER BY idDireccion",
			// this,idDireccion);
		} catch (Exception e) {
			logger.error("Error: DIRECCION, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return direccion;
	}

	/**
	 * Returns all rows from the DIRECCION table that match the criteria 'calle
	 * = :calle'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereCalleEquals(String
	 *                calle) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() + " WHERE calle = ? ORDER BY calle",
	 *                this,calle); } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria 'numExt
	 * = :numExt'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereNumExtEquals(String
	 *                numExt) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() + " WHERE numExt = ? ORDER BY numExt",
	 *                this,numExt); } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria 'numInt
	 * = :numInt'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereNumIntEquals(String
	 *                numInt) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() + " WHERE numInt = ? ORDER BY numInt",
	 *                this,numInt); } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria
	 * 'colonia = :colonia'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereColoniaEquals(String
	 *                colonia) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() + " WHERE colonia = ? ORDER BY colonia",
	 *                this,colonia); } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria
	 * 'delegacion = :delegacion'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereDelegacionEquals(String
	 *                delegacion) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() +
	 *                " WHERE delegacion = ? ORDER BY delegacion",
	 *                this,delegacion); } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria 'cp =
	 * :cp'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereCpEquals(String cp)
	 *                throws DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() + " WHERE cp = ? ORDER BY cp", this,cp);
	 *                } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria
	 * 'entidadFed = :entidadFed'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereEntidadFedEquals(String
	 *                entidadFed) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() +
	 *                " WHERE entidadFed = ? ORDER BY entidadFed",
	 *                this,entidadFed); } catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the DIRECCION table that match the criteria
	 * 'telefono = :telefono'.
	 * 
	 * @Transactional public List<DireccionDTO> findWhereTelefonoEquals(String
	 *                telefono) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
	 *                + getTableName() +
	 *                " WHERE telefono = ? ORDER BY telefono", this,telefono); }
	 *                catch (Exception e) {
	 *                logger.error("Error: DIRECCION, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	@SuppressWarnings("unchecked")
	/** 
	 * Returns all rows from the DIRECCION table that match the criteria 'idTipoDireccion = :idTipoDireccion'.
	 */
	@Transactional
	public List<DireccionDTO> findWhereIdTipoDireccionEquals(
			final int idTipoDireccion) throws DaoException {
		List<DireccionDTO> direccion = null;
		try {
			direccion = (List<DireccionDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							// TODO Auto-generated method stub
							Query oQuery = session
									.createQuery("SELECT idDireccion, calle, numExt,"
											+ " numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion"
											+ " FROM "
											+ getTableName()
											+ " WHERE idTipoDireccion = id_tipo ORDER BY idTipoDireccion");
							oQuery.setInteger(0, idTipoDireccion);
							List<DireccionDTO> direccionDto = (List<DireccionDTO>) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(DireccionDTO.class));
							return direccionDto;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idDireccion, calle, numExt, numInt, colonia, delegacion, cp, entidadFed, telefono, idTipoDireccion FROM "
			// + getTableName() +
			// " WHERE idTipoDireccion = ? ORDER BY idTipoDireccion",
			// this,idTipoDireccion);
		} catch (Exception e) {
			logger.error("Error: DIRECCION, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return direccion;
	}

	/**
	 * Returns the rows from the DIRECCION table that matches the specified
	 * primary-key value.
	 */
	public DireccionDTO findByPrimaryKey(DireccionPkDTO pk) throws DaoException {
		return findByPrimaryKey(pk.getIdDireccion());
	}

	/* (non-Javadoc)
	 * @see com.sophossystems.client.asmon.dao.DireccionDao#findLastInsert()
	 */
	@Override
	public DireccionPkDTO findLastInsert() throws DaoException {
		DireccionPkDTO direccion = null;
		try {
			direccion = (DireccionPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(D.idDireccion) as idDireccion FROM "
											+ getTableName()
											+ " D ");
							
							DireccionPkDTO direccionDto = (DireccionPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(DireccionPkDTO.class)).list().get(0);
							return direccionDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: DIRECCION, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return direccion;

	}

}
